from flask import Flask, request, jsonify
import datetime
from datetime import datetime, timedelta
import deepseek
import requests
from openai import OpenAI
from pymongo import MongoClient
import mysql.connector
from flask_sqlalchemy import SQLAlchemy
import os
import sqlite3
from flask_cors import CORS
import pandas as pd
from flask_cors import CORS
from sqlalchemy import create_engine
from sqlalchemy import text
from datetime import datetime, timedelta, timezone
import hashlib  # 添加这行来导入hashlib模块
import psutil
from mysql.connector import Error
from pymongo.errors import ConnectionFailure

app = Flask(__name__)
CORS(app)

UPLOAD_FOLDER = "/home/lyy/photo"
os.makedirs(UPLOAD_FOLDER, exist_ok=True)

client = OpenAI(api_key="sk-NzE4LTExNTAwNjU4MTgxLTE3NDI1MTcyOTg3NzI=", base_url="https://api.scnet.cn/api/llm/v1")

db_url = 'mysql+mysqlconnector://lyy:qwa678@localhost/health_db'
engine = create_engine(db_url)

# 微信配置
APPID = 'wxdb788eb02ad84ce6'
SECRET = 'c7370de3b31fa749f6234f93a6b6fe96'

# 配置 MySQL 连接
mysql_client = mysql.connector.connect(
    host="localhost",
    user="lyy",
    password="qwa678",
    database="health_db"
)

def get_db_connection():
    try:
        connection = mysql.connector.connect(
            host="localhost",
            user="lyy",
            password="qwa678",
            database="health_db"
        )
        if connection.is_connected():
            return connection
    except Error as e:
        print(f"Error while connecting to MySQL: {e}")
        return None

# 确保数据库中有 photo 表
def ensure_photo_table_exists():
    connection = get_db_connection()
    cursor = connection.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS photo (
            id INT AUTO_INCREMENT PRIMARY KEY,
            openid VARCHAR(255) NOT NULL,
            time DATETIME NOT NULL,
            image BLOB NOT NULL
        )
    """)
    connection.commit()
    cursor.close()
    connection.close()

ensure_photo_table_exists()


# 连接MongoDB，定义一个数据库和五个集合
mongo_client = MongoClient('mongodb://localhost:27017/')
main_db = mongo_client['main_db']
#machine_id_collection = main_db['machine_id']
data_collection = main_db['data']
error_collection = main_db['error']
calorie_collection = main_db['calorie']
distance_collection = main_db['distance']
match_collection = main_db['match']

#使用凯撒密码解密文本
def caesar_cipher_decrypt(text, shift=3):
    """使用凯撒密码解密文本"""
    decrypted_text = ""
    for char in text:
        if char.isalpha():
            shift_base = 65 if char.isupper() else 97
            decrypted_text += chr((ord(char) - shift_base - shift) % 26 + shift_base)
        else:
            decrypted_text += char
    return decrypted_text


##模拟器端api接口

# 1. 注册机器码
@app.route('/register', methods=['POST'])
def register_machine():
    # 获取数据包
    data = request.json.get('data')
    if not data or not isinstance(data, dict):
        return jsonify({"code": "-1", "message": "Invalid or missing data"}), 400

    try:
        machine_id = data.get('machine_id')  
        time_str = data.get('time')
        check_sum = int(data.get('check_sum'))
        
        # 检查必需字段是否存在
        if not all([machine_id, time_str, check_sum]):
            raise ValueError("Missing required fields")
    except Exception as e:
        return jsonify({"code": "-1", "message": f"Data parsing failed: {str(e)}"}), 400

    # 校验机器码是否已注册
    cursor = mysql_client.cursor()
    cursor.execute("SELECT * FROM machine_id WHERE machine_id = %s", (machine_id,))
    existing_machine = cursor.fetchone()
    cursor.close()

    if existing_machine:
        return jsonify({"code": "0", "message": "Machine_id has been registered"}), 400
    
    # 计算校验和并验证
    expected_check_sum = sum(ord(c) for c in f"{machine_id}{time_str}") % 100
    if check_sum != expected_check_sum:
        return jsonify({"code": "-3", "message": "Checksum verification failed"}), 400
    
    # 检查时间间隔
    upload_time = datetime.strptime(time_str, '%Y%m%d%H%M%S')
    if datetime.now() - upload_time > timedelta(hours=2):
        return jsonify({"code": "-4", "message": "Time expiration"}), 400
    
    # 写入数据库
    cursor = mysql_client.cursor()
    try:
        cursor.execute("INSERT INTO machine_id (machine_id, time) VALUES (%s, %s)", (machine_id, upload_time))
        mysql_client.commit()
        cursor.close()
        return jsonify({"code": "0", "message": "Success"}), 201
    except Exception as e:
        cursor.close()
        return jsonify({"code": "-5", "message": f"Database write failed: {str(e)}"}), 500

# 2. 上传数据
@app.route('/upload', methods=['POST'])
def upload_data():
    data = request.json.get('data')
    machine_id = request.json.get('machine_id')  # 直接从根对象获取machine_id
    
    if not data or not machine_id:
        return jsonify({"code": "-1", "message": "No machine_id"}), 400

    try:
        time_str = data.get('time')
        check_sum = int(data.get('check_sum'))
        
        # 检查必需字段是否存在
        if not all([time_str]):
            raise ValueError("Missing required fields")
    except Exception as e:
        return jsonify({"code": "-1", "message": f"No machine_id or missing required fields: {str(e)}"}), 400

    # 校验机器码是否已注册
    cursor = mysql_client.cursor()
    cursor.execute("SELECT * FROM machine_id WHERE machine_id = %s", (machine_id,))
    existing_machine = cursor.fetchone()
    cursor.close()
    if not existing_machine:
        return jsonify({"code": "-1", "message": "No machine_id"}), 400

    # 计算校验和并验证
    concatenated_str = (time_str +
                        data.get('heart_rate') +
                        data.get('breath_rate')+
                        data.get('body_temp') +
                        data.get('blood_pressure') +
                        data.get('step_count') +
                        data.get('distance') +
                        data.get('calories'))
    expected_check_sum = sum(ord(c) for c in concatenated_str) % 100
    print(f"Expected check_sum: {expected_check_sum}, Received check_sum: {check_sum}")  # Debug output
    if check_sum != expected_check_sum:
        return jsonify({"code": "-3", "message": "Data failed"}), 400

    # 检查时间间隔
    upload_time = datetime.strptime(time_str, '%Y%m%d%H%M%S')
    if datetime.now() - upload_time > timedelta(hours=2):
        return jsonify({"code": "-4", "message": "Time expiration"}), 400

    # 写入数据库
    new_data = {
        "pk_id": f"{machine_id}_{time_str}",
        "machine_id": machine_id,
        "time": upload_time,
        "heart_rate": int(data.get('heart_rate')),
        "breath_rate": int(data.get('breath_rate')),
        "body_temp": float(data.get('body_temp')),
        "blood_pressure": data.get('blood_pressure'),  # 字符串
        "steps": int(data.get('step_count')),
        "distance": float(data.get('distance')),
        "calories": float(data.get('calories'))
    }
    try:
        data_collection.insert_one(new_data)
    except Exception as e:
        return jsonify({"code": "-5", "message": "Write failed"}), 500

    return jsonify({"code": "0", "message": "Success"}), 201

# 3. 发送单次查询请求
def get_registered_machines():
    try:
        cursor = mysql_client.cursor()
        cursor.execute("SELECT machine_id FROM machine_id")
        machines = [row[0] for row in cursor.fetchall()]
        cursor.close()
        return machines
    except Exception as e:
        print(f"Error fetching machine IDs: {e}")
        return []

@app.route('/query', methods=['POST'])
def query_data():
    req_data = request.json
    print(f"Received request data: {req_data}")  # 调试信息

    # 获取请求参数
    data = req_data.get('data')
    if not data or 'query_time_start' not in data or 'query_time_end' not in data or 'time' not in data or 'machine_id' not in req_data:
        return jsonify({"code": "-1", "message": "Missing required fields"}), 400

    query_time_start_str = data.get('query_time_start')
    query_time_end_str = data.get('query_time_end')
    raw_time_str = data.get('time')
    time_str = raw_time_str.strip() if isinstance(raw_time_str, str) and raw_time_str is not None else None  # 安全地去除空格
    machine_id = req_data.get('machine_id')

    if not all([query_time_start_str, query_time_end_str, time_str, machine_id]):
        return jsonify({"code": "-1", "message": "Missing required field(s)"}), 400
    
    # 获取所有已注册的机器码
    registered_machines = get_registered_machines()

    # 判断机器码是否已经注册
    if machine_id not in registered_machines:
        return jsonify({"code": "-1", "message": "No machine_id"}), 400

    try:
        query_time_start = datetime.strptime(query_time_start_str, '%Y%m%d%H%M%S')
        query_time_end = datetime.strptime(query_time_end_str, '%Y%m%d%H%M%S')
        upload_time = datetime.strptime(time_str, '%Y%m%d%H%M%S')
        
        # 校验上传时间与当前时间间隔不超过2小时
        if datetime.now() - upload_time > timedelta(hours=2):
            return jsonify({"code": "-4", "message": "Time expiration"}), 400
        
        # 在数据库中查找数据（主键：machine_id+query_time）
        existing_data = list(data_collection.find({
        "machine_id": machine_id,
        "time": {"$gte": query_time_start, "$lte": query_time_end}
        }))
        if not existing_data:
            return jsonify({"code": "-5", "message": "No find data"}), 404

        response_data = []
        for data_item in existing_data:  # 改名为data_item避免与data变量冲突
            processed_data = {
                "machine_id": data_item["machine_id"],
                "time": data_item["time"].strftime('%Y%m%d%H%M%S'),
                # 添加其他需要的字段
            }
            # 或者如果你想要保留所有字段（除了_id）
            processed_data = {k: v.strftime('%Y%m%d%H%M%S') if isinstance(v, datetime) else v 
                            for k, v in data_item.items() if k != '_id'}
            response_data.append(processed_data)
            
        return jsonify({
            "code": "0",
            "message": "Success",
            "data": response_data
        }), 200

    except ValueError:
        return jsonify({"code": "-2", "message": "Invalid time format"}), 400
    except Exception as e:
        return jsonify({"code": "-3", "message": f"Unexpected error: {str(e)}"}), 400

# 4. 获取饮食建议
def generate_diet_advice(data_features):
    """向AI发送用户数据并获取饮食建议"""
    response = client.chat.completions.create(
        model="DeepSeek-R1-Distill-Qwen-7B",
        messages=[
            {"role": "system", "content": "请直接输出饮食建议，不要分析解释。用'建议：'开头，保持简洁。示例格式：建议：1.每日500克蔬菜 2.每周3次深海鱼类 3.限制精制糖..."},
            {"role": "user", "content": f"用户数据：{data_features}"},
        ],
        stream=False,
    )
    if response.choices:
        advice = response.choices[0].message.content.strip()
        
        # 格式标准化处理
        if "建议：" not in advice:
            if "：" in advice:  # 处理全角冒号变异
                advice = advice.split("：", 1)[-1].strip()
                advice = "建议：" + advice
            else:  # 无前缀时自动补全
                advice = "建议：" + advice
        else:
            advice = "建议：" + advice.split("建议：", 1)[-1].strip()
            
        return advice
    raise Exception("AI服务无响应")

@app.route('/dietAdvice', methods=['POST'])
def get_diet_advice():
    req_data = request.json
    time_str = req_data.get('time')
    machine_id = req_data.get('machine_id')

    if not all([time_str, machine_id]):
        return jsonify({"code": "-1", "message": "Missing required fields"}), 400

    try:
        # 检查机器ID是否存在
        cursor = mysql_client.cursor()
        cursor.execute("SELECT * FROM machine_id WHERE machine_id = %s", (machine_id,))
        existing_machine = cursor.fetchone()
        cursor.close()
        if not existing_machine:
            return jsonify({"code": "-1", "message": "No machine_id"}), 400

        upload_time = datetime.strptime(time_str, '%Y%m%d%H%M%S')
        if datetime.now() - upload_time > timedelta(hours=2):
            return jsonify({"code": "-2", "message": "Time expiration"}), 400

        # 修改为查询最近7天数据
        query_time_start = upload_time - timedelta(days=7)
        query_time_end = upload_time
        
        past_data = list(data_collection.find({
            "machine_id": machine_id,
            "time": {"$gte": query_time_start, "$lte": query_time_end}
        }))

        if not past_data:
            return jsonify({"code": "-5", "message": "No relevant data found in last 7 days"}), 404

        # 准备特征数据 - 添加周统计信息
        weekly_stats = {
            "avg_heart_rate": 0,
            "avg_breath_rate": 0,
            "total_steps": 0,
            "total_distance": 0,
            "total_calories": 0,
            "records_count": len(past_data)
        }

        valid_records = []
        for data in past_data:
            features = {
                "heart_rate": data.get("heart_rate"),
                "breath_rate": data.get("breath_rate"),
                "body_temp": data.get("body_temp"),
                "blood_pressure": data.get("blood_pressure"),
                "steps": data.get("steps", 0),
                "distance": data.get("distance", 0),
                "calories": data.get("calories", 0)
            }
            
            # 累计周统计
            if features["heart_rate"]: weekly_stats["avg_heart_rate"] += features["heart_rate"]
            if features["breath_rate"]: weekly_stats["avg_breath_rate"] += features["breath_rate"]
            weekly_stats["total_steps"] += features["steps"]
            weekly_stats["total_distance"] += features["distance"]
            weekly_stats["total_calories"] += features["calories"]
            
            valid_records.append(features)

        # 计算平均值
        if weekly_stats["records_count"] > 0:
            weekly_stats["avg_heart_rate"] = round(weekly_stats["avg_heart_rate"] / weekly_stats["records_count"], 1)
            weekly_stats["avg_breath_rate"] = round(weekly_stats["avg_breath_rate"] / weekly_stats["records_count"], 1)

        # 准备AI输入数据
        ai_input = {
            "weekly_stats": weekly_stats,
            "daily_records": valid_records
        }

        # 获取建议
        diet_advice = generate_diet_advice(ai_input)

        return jsonify({
            "code": "0",
            "message": "Success",
            "advice": diet_advice,
            "stats": weekly_stats  # 可选：返回统计信息给客户端
        }), 200

    except ValueError as e:
        app.logger.error(f"Value error: {str(e)}")
        return jsonify({"code": "-2", "message": "Invalid time format"}), 400
    except Exception as e:
        app.logger.error(f"Error in diet advice: {str(e)}", exc_info=True)
        return jsonify({"code": "-3", "message": "Internal server error"}), 500

# 5. 获取运动建议
def generate_exercise_advice(data_features):
    """向AI发送用户数据并获取运动建议"""
    response = client.chat.completions.create(
        model="DeepSeek-R1-Distill-Qwen-7B",
        messages=[
            {"role": "system", "content": "请直接输出运动建议，不要分析解释。用'建议：'开头，保持简洁。示例格式：建议：1.每日慢跑30分钟 2.每周3次力量训练..."},
            {"role": "user", "content": f"用户数据：{data_features}"},
        ],
        stream=False,
    )
    if response.choices:
        advice = response.choices[0].message.content.strip()
        
        # 确保只保留建议部分
        if "建议：" in advice:
            advice = advice.split("建议：", 1)[-1].strip()  # 移除前缀
            advice = "建议：" + advice  # 保持格式统一
        return advice
    raise Exception("AI服务无响应")
    
@app.route('/exerciseAdvice', methods=['POST'])
def get_exercise_advice():
    req_data = request.json
    time_str = req_data.get('time')
    machine_id = req_data.get('machine_id')

    if not all([time_str, machine_id]):
        return jsonify({"code": "-1", "message": "Missing required fields"}), 400

    try:
        # 检查机器ID是否存在（修改为查询MySQL数据库）
        cursor = mysql_client.cursor()
        query = "SELECT * FROM machine_id WHERE machine_id = %s"
        cursor.execute(query, (machine_id,))
        existing_machine = cursor.fetchone()
        cursor.close()
        if not existing_machine:
            return jsonify({"code": "-1", "message": "No machine_id"}), 400

        upload_time = datetime.strptime(time_str, '%Y%m%d%H%M%S')
        if datetime.now() - upload_time > timedelta(hours=2):
            return jsonify({"code": "-2", "message": "Time expiration"}), 400

        # 查询最近7天数据
        query_time_start = upload_time - timedelta(days=7)
        query_time_end = upload_time

        past_data = list(data_collection.find({
            "machine_id": machine_id,
            "time": {"$gte": query_time_start, "$lte": query_time_end}
        }))

        if not past_data:
            return jsonify({"code": "-5", "message": "No relevant data found in last 7 days"}), 404

        # 准备运动相关统计数据
        exercise_stats = {
            "avg_heart_rate": 0,
            "total_active_minutes": 0,
            "total_steps": 0,
            "total_distance": 0,
            "total_calories_burned": 0,
            "active_days": 0,
            "records_count": len(past_data)
        }

        active_days = set()  # 记录有运动的天数
        for data in past_data:
            # 转换时间为日期格式
            record_date = data["time"].date()

            if data.get("heart_rate"):
                exercise_stats["avg_heart_rate"] += data["heart_rate"]

            exercise_stats["total_steps"] += data.get("steps", 0)
            exercise_stats["total_distance"] += data.get("distance", 0)
            exercise_stats["total_calories_burned"] += data.get("calories", 0)

            # 如果有运动量则计入活跃日
            if data.get("steps", 0) > 3000 or data.get("distance", 0) > 2:  # 假设步数>3000或距离>2km算活跃
                active_days.add(record_date)
                exercise_stats["total_active_minutes"] += data.get("active_minutes", 30)  # 默认30分钟

        exercise_stats["active_days"] = len(active_days)
        if exercise_stats["records_count"] > 0:
            exercise_stats["avg_heart_rate"] = round(exercise_stats["avg_heart_rate"] / exercise_stats["records_count"], 1)

        # 准备AI输入数据
        ai_input = {
            "exercise_stats": exercise_stats,
            "activity_trend": {
                "most_active_day": max(active_days) if active_days else None,
                "least_active_day": min(active_days) if active_days else None
            }
        }

        # 获取运动建议
        exercise_advice = generate_exercise_advice(ai_input)

        return jsonify({
            "code": "0",
            "message": "Success",
            "advice": exercise_advice,
            "stats": exercise_stats  # 可选：返回统计信息
        }), 200

    except ValueError:
        return jsonify({"code": "-2", "message": "Invalid time format"}), 400
    except Exception as e:
        app.logger.error(f"Exercise advice error: {str(e)}", exc_info=True)
        return jsonify({"code": "-3", "message": "Internal server error"}), 500

# 6. 上传设备错误信息
@app.route('/errorReport', methods=['POST'])
def report_error():
    payload = request.json
    data = payload.get('data')
    machine_id = payload.get('machine_id')

    # 检查机器ID是否存在（修改为查询MySQL数据库）
    cursor = mysql_client.cursor()
    query = "SELECT * FROM machine_id WHERE machine_id = %s"
    cursor.execute(query, (machine_id,))
    existing_machine = cursor.fetchone()
    cursor.close()
    if not existing_machine:
        return jsonify({"code": "-1", "message": "No machine_id"}), 400

    upload_time_str = data.get('time')
    try:
        upload_time = datetime.strptime(upload_time_str, '%Y%m%d%H%M%S')
    except ValueError:
        return jsonify({"code": "-2", "message": "Time format error"}), 400

    current_time = datetime.now()
    if current_time - upload_time > timedelta(hours=2):
        return jsonify({"code": "-2", "message": "Time expiration"}), 400

    error_message = data.get('error')
    if not error_message:
        return jsonify({"code": "-3", "message": "Error message is required"}), 400

    # 生成 pk_id
    pk_id = f"{machine_id}{upload_time_str}"

    new_error = {
        "pk_id": pk_id,
        "machine_id": machine_id,
        "time": upload_time,
        "error": error_message
    }
    try:
        error_collection.insert_one(new_error)
    except Exception as e:
        return jsonify({"code": "-3", "message": "Write failed"}), 500

    return jsonify({"code": "0", "message": "Success"}), 201

# 7. 设置卡路里目标
@app.route('/setCalorieGoal', methods=['POST'])
def upload_calories():
    req_data = request.json

    # 获取machine_id和data对象
    machine_id = req_data.get('machine_id')
    data = req_data.get('data')

    # 验证必要字段是否存在
    if not all(key in data for key in ('time', 'calorie_target')) or machine_id is None:
        return jsonify({"code": "-1", "message": "Missing required fields"}), 400

    # 检查机器ID是否已注册（修改为查询MySQL数据库）
    cursor = mysql_client.cursor()
    query = "SELECT * FROM machine_id WHERE machine_id = %s"
    cursor.execute(query, (machine_id,))
    existing_machine = cursor.fetchone()
    cursor.close()
    if not existing_machine:
        return jsonify({"code": "-1", "message": "No machine_id"}), 400

    try:
        # 创建新的Calorie记录
        new_calorie = {
            "pk_id": f"{machine_id}_{data['time']}",
            "machine_id": machine_id,
            "time": datetime.strptime(data['time'], '%Y%m%d%H%M%S'),
            "calorie_target": int(data['calorie_target'])  # 统一使用小写字段名
        }

        # 使用update_one实现upsert，避免重复插入
        calorie_collection.update_one(
            {"pk_id": new_calorie["pk_id"]},
            {"$set": new_calorie},
            upsert=True
        )

        return jsonify({
            "code": "0",
            "message": "Calorie target set successfully!"
        }), 201

    except ValueError:
        return jsonify({"code": "-2", "message": "Invalid time format"}), 400
    except Exception as e:
        return jsonify({"code": "-3", "message": str(e)}), 500

# 8. 设置运动距离目标
@app.route('/setDistanceGoal', methods=['POST'])
def upload_distance():
    req_data = request.json

    machine_id = req_data.get('machine_id')
    data = req_data.get('data')

    # 验证必要字段是否存在
    if not all(key in data for key in ('time', 'distance_target')) or machine_id is None:
        return jsonify({"code": "-1", "message": "Missing required fields"}), 400

    # 检查机器ID是否已注册（修改为查询MySQL数据库）
    cursor = mysql_client.cursor()
    query = "SELECT * FROM machine_id WHERE machine_id = %s"
    cursor.execute(query, (machine_id,))
    existing_machine = cursor.fetchone()
    cursor.close()
    if not existing_machine:
        return jsonify({"code": "-1", "message": "No machine_id"}), 400

    try:
        # 创建新的Distance记录
        new_distance = {
            "pk_id": f"{machine_id}_{data['time']}",
            "machine_id": machine_id,
            "time": datetime.strptime(data['time'], '%Y%m%d%H%M%S'),
            "distance_target": int(data['distance_target'])
        }

        # 使用update_one实现upsert
        distance_collection.update_one(
            {"pk_id": new_distance["pk_id"]},
            {"$set": new_distance},
            upsert=True
        )

        return jsonify({
            "code": "0",
            "message": "Distance target set successfully!"
        }), 201

    except ValueError:
        return jsonify({"code": "-2", "message": "Invalid time format"}), 400
    except Exception as e:
        return jsonify({"code": "-3", "message": str(e)}), 500

# 9. 获取剩余运动距离
@app.route('/getRemainingDistance', methods=['POST'])
def get_remaining_distance():
    req_data = request.json
    machine_id = req_data.get('machine_id')
    upload_time_str = req_data.get('time')

    # 验证必要字段
    if not machine_id or not upload_time_str:
        return jsonify({"code": "-1", "message": "Missing required fields"}), 400

    # 检查机器ID是否已注册（修改为查询MySQL数据库）
    cursor = mysql_client.cursor()
    query = "SELECT * FROM machine_id WHERE machine_id = %s"
    cursor.execute(query, (machine_id,))
    existing_machine = cursor.fetchone()
    cursor.close()
    if not existing_machine:
        return jsonify({"code": "-1", "message": "No machine_id"}), 400

    try:
        upload_time = datetime.strptime(upload_time_str, '%Y%m%d%H%M%S')

        # 检查时间有效性
        if datetime.now() - upload_time > timedelta(hours=2):
            return jsonify({"code": "-2", "message": "Time expiration"}), 400

        # 获取最新的距离目标
        last_distance_record = distance_collection.find_one(
            {"machine_id": machine_id},
            sort=[("time", -1)]
        )

        if not last_distance_record:
            return jsonify({"code": "-3", "message": "No distance target set"}), 400

        start_time = last_distance_record["time"]
        distance_target = float(last_distance_record["distance_target"])

        # 计算累计距离
        pipeline = [
            {"$match": {
                "machine_id": machine_id,
                "time": {"$gte": start_time}
            }},
            {"$group": {
                "_id": None,
                "total_distance": {"$sum": "$distance"}
            }}
        ]

        result = list(data_collection.aggregate(pipeline))

        total_distance = result[0]["total_distance"] if result else 0.0

        remaining_distance = distance_target - total_distance

        if remaining_distance <= 0:
            return jsonify({
                "code": "1",
                "message": "Success",
                "data": {"remaining_distance": 0, "status": "target_achieved"}
            })
        else:
            return jsonify({
                "code": "0",
                "message": "Success",
                "data": {
                    "remaining_distance": round(remaining_distance, 2),
                    "target_distance": distance_target,
                    "progress": round((total_distance / distance_target) * 100, 1)
                }
            })

    except ValueError:
        return jsonify({"code": "-2", "message": "Invalid time format"}), 400
    except Exception as e:
        return jsonify({"code": "-3", "message": str(e)}), 500

# 10. 获取剩余卡路里值
@app.route('/getRemainingCalories', methods=['POST'])
def get_remaining_calories():
    req_data = request.json
    machine_id = req_data.get('machine_id')
    upload_time_str = req_data.get('time')

    # 验证必要字段
    if not machine_id or not upload_time_str:
        return jsonify({"code": "-1", "message": "Missing required fields"}), 400

    # 检查机器ID是否已注册（修改为查询MySQL数据库）
    cursor = mysql_client.cursor()
    query = "SELECT * FROM machine_id WHERE machine_id = %s"
    cursor.execute(query, (machine_id,))
    existing_machine = cursor.fetchone()
    cursor.close()
    if not existing_machine:
        return jsonify({"code": "-1", "message": "No machine_id"}), 400

    try:
        upload_time = datetime.strptime(upload_time_str, '%Y%m%d%H%M%S')

        # 检查时间有效性
        if datetime.now() - upload_time > timedelta(hours=2):
            return jsonify({"code": "-2", "message": "Time expiration"}), 400

        # 获取最新的卡路里目标
        last_calorie_record = calorie_collection.find_one(
            {"machine_id": machine_id},
            sort=[("time", -1)]
        )

        if not last_calorie_record:
            return jsonify({"code": "-3", "message": "No calorie target set"}), 400

        start_time = last_calorie_record["time"]
        calorie_target = float(last_calorie_record["calorie_target"])

        # 计算累计卡路里消耗
        pipeline = [
            {"$match": {
                "machine_id": machine_id,
                "time": {"$gte": start_time}
            }},
            {"$group": {
                "_id": None,
                "total_calories": {"$sum": "$calories"}
            }}
        ]

        result = list(data_collection.aggregate(pipeline))
        total_calories = result[0]["total_calories"] if result else 0.0

        remaining_calories = calorie_target - total_calories

        if remaining_calories <= 0:
            return jsonify({
                "code": "1",
                "message": "Success",
                "data": {"remaining_calories": 0, "status": "target_achieved"}
            })
        else:
            return jsonify({
                "code": "0",
                "message": "Success",
                "data": {
                    "remaining_calories": round(remaining_calories, 2),
                    "target_calories": calorie_target,
                    "progress": round((total_calories / calorie_target) * 100, 1)
                }
            })

    except ValueError:
        return jsonify({"code": "-2", "message": "Invalid time format"}), 400
    except Exception as e:
        return jsonify({"code": "-3", "message": str(e)}), 500


##小程序端api接口

# 1. 微信登录
@app.route('/login', methods=['GET'])
def wx_login():
    code = request.args.get('code')
    if not code:
        return jsonify({"code": 400, "message": "Missing code parameter"}), 400

    # 请求微信接口获取 openid 和 session_key
    url = f'https://api.weixin.qq.com/sns/jscode2session?appid={APPID}&secret={SECRET}&js_code={code}&grant_type=authorization_code'
    try:
        response = requests.get(url)
        response.raise_for_status()
        data = response.json()

        if 'errcode' in data:
            return jsonify({"code": 401, "message": f"微信验证失败: {data.get('errmsg')}"}), 401

        openid = data.get('openid')
        session_key = data.get('session_key')

        # 检查是否已经存在这个 openid
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # 查询是否存在该 openid
        cursor.execute("SELECT * FROM Wxuser_data WHERE Openid = %s", (openid,))
        existing_user = cursor.fetchone()

        if not existing_user:
            # 插入新用户（只填写必须字段）
            cursor.execute("""
                INSERT INTO Wxuser_data (Openid, Session_key)
                VALUES (%s, %s)
            """, (openid, session_key))
            conn.commit()
            print(f"新用户已注册: {openid}")
        else:
            print(f"用户已存在: {openid}")

        cursor.close()
        conn.close()

        return jsonify({
            "code": 200,
            "message": "登录成功",
            "openid": openid,
            "session_key": session_key
        })

    except requests.RequestException as e:
        return jsonify({"code": 500, "message": f"请求微信接口失败: {str(e)}"}), 500
        

# 2. 获取用户信息
@app.route('/getuserdata', methods=['GET'])
def get_userdata():
    # 从请求头中获取 openid（假设前端登录后将 openid 存入 header）
    openid = request.headers.get('X-Openid')
    if not openid:
        return jsonify({"code": 400, "message": "Missing openid"}), 400

    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    try:
        cursor.execute("SELECT * FROM Wxuser_data WHERE Openid = %s", (openid,))
        user = cursor.fetchone()

        if user:
            return jsonify({
                "code": 200,
                "data": {
                    "gender": user["Gender"],
                    "age": user["Age"],
                    "height": user["Height"],
                    "birthday": user["Birthday"].strftime("%Y-%m-%d") if user["Birthday"] else None,
                    "hobby": user["Hobby"]
                }
            })
        else:
            # 用户不存在，返回空数据
            return jsonify({"code": 200, "data": {}})
    finally:
        cursor.close()
        conn.close()

# 3. 保存用户信息
@app.route('/saveuserdata', methods=['POST'])
def save_userdata():
    openid = request.headers.get('X-Openid')
    if not openid:
        return jsonify({"code": 400, "message": "Missing openid"}), 400

    data = request.json
    gender = data.get("gender")
    age = data.get("age")
    height = data.get("height")
    birthday = data.get("birthday")
    hobby = data.get("hobby")

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        update_sql = """
        UPDATE Wxuser_data SET
            Gender = %s,
            Age = %s,
            Height = %s,
            Birthday = %s,
            Hobby = %s
        WHERE Openid = %s
        """

        cursor.execute(update_sql, (
            gender,
            age if age is not None else None,
            height if height else None,
            birthday if birthday else None,
            hobby if hobby else None,
            openid
        ))

        conn.commit()

        if cursor.rowcount == 0:
            return jsonify({"code": 404, "message": "用户不存在"}), 404

        return jsonify({"code": 200, "message": "保存成功"})
    except Exception as e:
        conn.rollback()
        return jsonify({"code": 500, "message": f"保存失败: {str(e)}"}), 500
    finally:
        cursor.close()
        conn.close()

# 4. 查找是否有匹配的设备
@app.route('/finddevice', methods=['GET'])
def find_device():
    openid = request.headers.get('X-Openid')
    if not openid:
        return jsonify({'success': False, 'message': '缺少 X-Openid 请求头'}), 400

    connection = get_db_connection()
    try:
        with connection.cursor() as cursor:
            sql = "SELECT machine_id FROM Wxuser_data WHERE openid = %s"
            cursor.execute(sql, (openid,))
            result = cursor.fetchone()

            if result:
                machine_id = result[0]  # 使用索引访问元组
                if machine_id and machine_id != '0':
                    return jsonify({
                        'success': True,
                        'machine_id': machine_id
                    })
                else:
                    return jsonify({
                        'success': True,
                        'machine_id': '0'
                    })
            else:
                return jsonify({
                    'success': True,
                    'machine_id': '0'
                })
    finally:
        connection.close()


# 5. 匹配设备
@app.route('/matchdevice', methods=['POST'])
def match_device():
    openid = request.headers.get('X-Openid')
    data = request.get_json()
    new_machine_id = data.get('machine_id')

    if not openid or not new_machine_id:
        return jsonify({'success': False, 'message': '缺少参数'}), 400

    connection = get_db_connection()
    try:
        with connection.cursor() as cursor:

            # 检查设备是否存在
            sql_check_device_exists = "SELECT COUNT(*) FROM machine_id WHERE machine_id = %s"
            cursor.execute(sql_check_device_exists, (new_machine_id,))
            device_exists = cursor.fetchone()[0]

            if not device_exists:
                return jsonify({'success': False, 'message': '设备不存在'}), 404

            # 检查当前设备是否已被其他用户绑定
            sql_check_binding = "SELECT openid FROM machine_id WHERE machine_id = %s"
            cursor.execute(sql_check_binding, (new_machine_id,))
            old_user = cursor.fetchone()

            if old_user and old_user[0] != '0' and old_user[0] is not None:
                # 解除原绑定用户的设备绑定
                sql_update_old = "UPDATE Wxuser_data SET machine_id = '0' WHERE openid = %s"
                cursor.execute(sql_update_old, (old_user[0],))

            # 更新设备表 machine_id，将设备绑定到新的 openid
            sql_update_machine = """
                UPDATE machine_id 
                SET openid = %s 
                WHERE machine_id = %s
            """
            cursor.execute(sql_update_machine, (openid, new_machine_id))

            # 更新当前用户表 Wxuser_data，设置其绑定设备
            sql_update_user = """
                UPDATE Wxuser_data 
                SET machine_id = %s 
                WHERE openid = %s
            """
            cursor.execute(sql_update_user, (new_machine_id, openid))

            connection.commit()
            return jsonify({'success': True})

    except Exception as e:
        connection.rollback()
        return jsonify({'success': False, 'message': str(e)}), 500

    finally:
        connection.close()

# 6. 获得最近10条体重数据
@app.route('/getweight', methods=['POST'])
def get_weight():
    data = request.get_json()
    openid = data.get('openid')
    
    if not openid:
        return jsonify({"error": "Missing openid"}), 400

    try:
        connection = get_db_connection()  # 使用统一配置的连接函数
        with connection.cursor(dictionary=True) as cursor:  # 设置为字典模式以方便访问列
            sql = """
            SELECT * FROM weight 
            WHERE openid = %s 
            ORDER BY time DESC 
            LIMIT 10
            """
            cursor.execute(sql, (openid,))
            results = cursor.fetchall()

            # 如果没有数据，返回空数组
            if not results:
                return jsonify([])

            weights = [
                {
                    'id': row['id'],
                    'openid': row['openid'],
                    'time': row['time'].isoformat(),
                    'weight': float(row['weight'])
                }
                for row in results
            ]

            return jsonify(weights)

    except Exception as e:
        app.logger.error(f"[ERROR] 查询失败: {e}")  # 使用Flask的日志记录器
        return jsonify({'error': '查询体重记录失败'}), 500

    finally:
        if 'connection' in locals() and connection.is_connected():
            connection.close()


# 7. 上传体重数据
@app.route('/uploadweight', methods=['POST'])
def upload_weight():
    data = request.get_json()

    if not data or 'openid' not in data or 'weight' not in data or 'time' not in data:
        return jsonify({'error': '缺少必要参数'}), 400

    openid = data['openid']
    weight_value = data['weight']
    time_str = data['time']

    try:
        # 将前端传来的字符串时间转为 datetime 对象（格式应为 YYYY-MM-DD HH:MM:SS）
        time = datetime.strptime(time_str, '%Y-%m-%d %H:%M:%S')
    except ValueError:
        return jsonify({'error': '时间格式错误，请使用 YYYY-MM-DD HH:MM:SS'}), 400

    # 使用已定义的 get_db_connection 函数来获取数据库连接
    connection = get_db_connection()
    
    try:
        with connection.cursor() as cursor:
            insert_sql = """
            INSERT INTO weight (openid, time, weight)
            VALUES (%s, %s, %s)
            """
            cursor.execute(insert_sql, (openid, time, float(weight_value)))
        connection.commit()
    except Exception as e:
        print(f"插入失败: {e}")
        connection.rollback()
        return jsonify({'error': '插入体重记录失败'}), 500
    finally:
        connection.close()

    return jsonify({'success': True})


# 8. 获取ai建议
@app.route('/aicoach', methods=['POST'])
def aicoach():
    data = request.get_json()
    openid = data.get('openid')
    question = data.get('question')

    if not openid or not question:
        return jsonify({"error": "缺少参数 openid 或 question"}), 400

    try:
        with engine.connect() as conn:
            # 查询用户基本信息及机器码
            result = conn.execute(
                text("SELECT Age, Gender, Height, Birthday, Hobby, machine_id FROM Wxuser_data WHERE Openid = :openid"),
                {"openid": openid}
            ).fetchone()

            if not result:
                return jsonify({"error": "无相关用户信息"}), 404

            user_info = result._asdict()
            machine_id = user_info.pop('machine_id')

            # 查询最新体重数据
            weight_result = conn.execute(
                text("SELECT weight FROM weight WHERE openid = :openid ORDER BY time DESC LIMIT 1"),
                {"openid": openid}
            ).fetchone()

            weight_info = weight_result[0] if weight_result else "无体重数据"

        # 查询 MongoDB 中的设备数据
        cursor = data_collection.find({"machine_id": machine_id}).sort("timestamp", -1).limit(10)
        collection_info = list(cursor)

        if not collection_info:
            collection_info = "无运动数据"
        else:
            # 如果需要导出为 DataFrame，也可以用：
            # pd.DataFrame(collection_info)
            pass

    except Exception as e:
        return jsonify({"error": f"数据库查询错误: {str(e)}"}), 500

    # 构造提示词
    prompt = f"""
        用户openid为{openid}，基本信息为{user_info}，注意，性别1是男，2是女，体重数据为{weight_info}。
        运动数据如下：{collection_info}
        用户的问题是：{question}

        请基于以上信息提供健康建议或回答用户问题，尽量保持回答在200字以内。
        ⚠️ 请你在正式回答前添加一行："开始回答："，不要包含其他格式或解释性文字。
        """

    try:
        response = client.chat.completions.create(
            model="DeepSeek-R1-Distill-Qwen-7B",
            messages=[{"role": "user", "content": prompt}],
            max_tokens=800
        )
        ai_answer = response.choices[0].message.content

        # 提取“开始回答：”之后的内容
        if "开始回答：" in ai_answer:
            ai_answer = ai_answer.split("开始回答：", 1)[1].strip()
        print("【准备返回】", {"answer": ai_answer})
        ai_answer = ai_answer.replace('\n', ' ').strip()  # 去掉换行符
        return jsonify({"answer": ai_answer})

    except Exception as e:
        print(f"[ERROR] 调用AI失败: {e}")
        return jsonify({"error": f"AI 请求错误: {str(e)}"}), 500

# 9. 获取运动数据
@app.route('/getsportdata', methods=['POST'])
def get_sport_data():
    # 获取请求中的 openid
    data = request.get_json()
    openid = data.get('openid')
    
    if not openid:
        return jsonify({"error": "Missing openid"}), 400
    
    try:
        # 连接到 MySQL 数据库并查找 machine_id
        connection = get_db_connection()
        cursor = connection.cursor(dictionary=True)
        
        query = "SELECT machine_id FROM Wxuser_data WHERE openid = %s"
        cursor.execute(query, (openid,))
        result = cursor.fetchone()
        
        if not result or 'machine_id' not in result:
            return jsonify({"error": "User not found"}), 404
        
        machine_id = result['machine_id']
        
        # 关闭 MySQL 连接
        cursor.close()
        connection.close()
        
        # 使用 machine_id 在 MongoDB 中查找相关数据，并按 _id 降序排序取第一条（即最新一条）
        latest_record = data_collection.find_one(
            {"machine_id": machine_id},
            sort=[("_id", -1)],     # 按 ObjectId 时间戳倒序排列
            projection={"_id": 0}   # 不返回 _id 字段
        )
        
        if not latest_record:
            return jsonify({"message": "暂无运动数据"}), 200
        
        # 返回找到的数据
        return jsonify(latest_record), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500

# 10. 保存用户打卡照片
@app.route('/savephoto', methods=['POST'])
def save_photo():
    openid = request.form.get('openid')
    time_str = request.form.get('time')
    file = request.files.get('file')

    if not all([openid, time_str, file]):
        return jsonify({"success": False, "message": "参数缺失"}), 400

    try:
        upload_time = datetime.strptime(time_str, "%Y-%m-%d")
    except ValueError:
        return jsonify({"success": False, "message": "时间格式错误，请使用 YYYY-MM-DD"}), 400

    # 文件名处理
    filename = f"{openid}_{datetime.now().strftime('%Y%m%d%H%M%S')}.jpg"
    filepath = os.path.join(UPLOAD_FOLDER, filename)

    # 保存文件
    file.save(filepath)

    # 存入数据库
    conn = get_db_connection()
    if conn is None:
        return jsonify({"success": False, "message": "数据库连接失败"}), 500
    cursor = conn.cursor()
    try:
        cursor.execute("""
            INSERT INTO photo (openid, time, filepath)
            VALUES (%s, %s, %s)
        """, (openid, upload_time, filepath))
        conn.commit()
    except Exception as e:
        conn.rollback()
        return jsonify({"success": False, "message": str(e)}), 500
    finally:
        cursor.close()
        conn.close()

    return jsonify({"success": True, "filepath": filepath}), 200


# 11. 获取用户打卡照片
@app.route('/getphoto', methods=['POST'])
def get_photo():
    data = request.get_json()
    openid = data.get('openid')

    if not openid:
        return jsonify({"success": False, "message": "缺少 openid"}), 400

    conn = get_db_connection()
    if conn is None:
        return jsonify({"success": False, "message": "数据库连接失败"}), 500

    cursor = conn.cursor(dictionary=True)
    try:
        cursor.execute("SELECT * FROM photo WHERE openid = %s ORDER BY time DESC", (openid,))
        photos = cursor.fetchall()
    finally:
        cursor.close()
        conn.close()

    return jsonify({"photos": photos}), 200


##web端api接口

# 1. web端登陆
@app.route('/weblogin', methods=['POST'])
def weblogin():
    # 获取前端提交的JSON数据
    data = request.get_json()
    if not data or 'username' not in data or 'password' not in data or 'role' not in data:
        return jsonify({
            'success': False,
            'message': '缺少必要参数: username/password/role'
        }), 400

    username = data['username']
    password = data['password']  # 直接使用明文密码
    role = data['role']

    # 验证参数有效性
    if not isinstance(username, str) or not isinstance(password, str) or not isinstance(role, str):
        return jsonify({
            'success': False,
            'message': '参数类型错误'
        }), 400

    if role not in ['admin', 'manager', 'user']:
        return jsonify({
            'success': False,
            'message': '无效的角色类型'
        }), 400

    # 连接数据库查询用户
    connection = get_db_connection()
    if not connection:
        return jsonify({
            'success': False,
            'message': '数据库连接失败'
        }), 500

    try:
        cursor = connection.cursor(dictionary=True)
        
        # 查询用户是否存在
        cursor.execute(
            "SELECT username, password_hash, role FROM webuser_data WHERE username = %s",
            (username,)
        )
        user = cursor.fetchone()

        if not user:
            return jsonify({'success': False, 'message': '用户名不存在'}), 401

        # 比较明文密码
        if password != user['password_hash']:  # 注意这里用了 password_hash 字段
            return jsonify({'success': False, 'message': '密码错误'}), 401

        # 验证角色
        if role != user['role']:
            return jsonify({
                'success': False,
                'message': '角色不匹配'
            }), 401

        ######################################################
        # ✅ 登录成功：更新用户状态为 active
        ######################################################
        cursor.execute(
            "UPDATE webuser_data SET status = 'active' WHERE username = %s",
            (username,)
        )
        connection.commit()  # 提交事务
        ######################################################

        # 登录成功
        return jsonify({
            'success': True,
            'message': '登录成功',
            'role': user['role'],
            'username': user['username']
        })

    except mysql.connector.Error as e:  # 使用正确的异常类型
        print(f"数据库查询错误: {e}")
        return jsonify({
            'success': False,
            'message': '数据库操作失败'
        }), 500
    finally:
        cursor.close()
        connection.close()

# 2. 获取用户列表
@app.route('/getwebuser', methods=['GET'])
def get_web_users():
    try:
        # 使用 SQLAlchemy 执行查询
        with engine.connect() as connection:
            result = connection.execute(text("SELECT id, username, role, status, email FROM webuser_data"))
            users = result.fetchall()

        result_list = []
        for user in users:
            result_list.append({
                'id': user.id,
                'username': user.username,
                'role': user.role,
                'status': user.status,
                'email': user.email
            })

        return jsonify(result_list)

    except Exception as e:
        print(f"数据库查询错误: {e}")
        return jsonify({'error': '获取用户列表失败'}), 500

# 3. 获取用户总数
@app.route('/getusercount', methods=['GET'])
def get_user_count():
    try:
        connection = get_db_connection()
        if not connection:
            return jsonify({"error": "无法连接到数据库"}), 500

        cursor = connection.cursor()
        cursor.execute("SELECT COUNT(*) FROM webuser_data") 
        count = cursor.fetchone()[0]

        cursor.close()
        connection.close()

        print(count)
        return jsonify({"count": count})
    except Exception as e:
        print(f"获取用户总数出错: {e}")
        return jsonify({"error": "服务器内部错误"}), 500

# 4. 保存修改的用户信息
@app.route('/savewebuserdata', methods=['POST'])
def save_user_data():
    try:
        data = request.get_json()
        print("收到的数据:", data)

        if data is None:
            return jsonify({'error': '请求体为空或不是有效的 JSON'}), 400

        user_id = data.get('id')
        username = data.get('username')
        role = data.get('role')
        status = data.get('status')
        email = data.get('email')

        if not user_id:
            return jsonify({'error': '用户ID不能为空'}), 400
        if not username:
            return jsonify({'error': '用户名不能为空'}), 400
        if role not in ['admin', 'manager', 'user']:
            return jsonify({'error': '角色不合法'}), 400
        if status not in ['active', 'inactive']:
            return jsonify({'error': '状态不合法'}), 400

        with engine.connect() as conn:
            conn.execute(
                text("""
                    UPDATE webuser_data
                    SET username = :username,
                        role = :role,
                        status = :status,
                        email = :email
                    WHERE id = :id
                """),
                {
                    'id': user_id,
                    'username': username,
                    'role': role,
                    'status': status,
                    'email': email
                }
            )
            conn.commit()

        return jsonify({'message': '保存成功'})

    except Exception as e:
        print(f"数据库操作错误: {e}")
        return jsonify({'error': '服务器内部错误'}), 500

# 5. 删除用户信息
@app.route('/deletewebuserdata', methods=['POST'])
def delete_web_user():
    try:
        data = request.get_json()
        user_id = data.get('id')

        if not user_id:
            return jsonify({'error': '缺少用户ID'}), 400

        with engine.connect() as conn:
            # 执行删除语句
            result = conn.execute(
                text("DELETE FROM webuser_data WHERE id = :id"),
                {'id': user_id}
            )
            conn.commit()

            if result.rowcount == 0:
                return jsonify({'error': '未找到该用户'}), 404

        return jsonify({'message': '删除成功'})

    except Exception as e:
        print(f"数据库操作错误: {e}")
        return jsonify({'error': '服务器内部错误'}), 500

# 6. 添加用户信息
@app.route('/addwebuserdata', methods=['POST'])
def add_web_user():
    try:
        data = request.get_json()
        print("收到的数据:", data)

        if not data:
            return jsonify({'error': '请求体为空或不是有效的 JSON'}), 400

        # 字段映射（前端用的是 active，后端要 status）
        username = data.get('username')
        email = data.get('email')
        role = data.get('role')
        active = data.get('active')  # 布尔值
        status = 'active' if active else 'inactive'

        # 校验必填字段
        if not username or not role:
            return jsonify({'error': '用户名和角色为必填项'}), 400
        if role not in ['admin', 'manager', 'user']:
            return jsonify({'error': '角色不合法'}), 400

        with engine.connect() as conn:
            conn.execute(
                text("""
                    INSERT INTO webuser_data (username, password_hash, email, role, status)
                    VALUES (:username, SHA2('defaultPassword123', 256), :email, :role, :status)
                """),
                {
                    'username': username,
                    'email': email,
                    'role': role,
                    'status': status
                }
            )
            conn.commit()

        return jsonify({'message': '用户添加成功'})

    except Exception as e:
        print(f"数据库操作错误: {e}")
        return jsonify({'error': '服务器内部错误'}), 500

# 7. 获取设备列表
@app.route('/getdevicelist', methods=['GET'])
def get_device_list():
    page = int(request.args.get('page', 1))
    size = int(request.args.get('size', 10))
    offset = (page - 1) * size

    try:
        connection = get_db_connection()
        if not connection:
            return jsonify({"error": "无法连接到数据库"}), 500

        cursor = connection.cursor(dictionary=True)
        cursor.execute("""
            SELECT machine_id AS id, openid 
            FROM machine_id 
            LIMIT %s OFFSET %s
        """, [size, offset])
        
        rows = cursor.fetchall()

        # 构造返回结果，包含 id、status、openid
        devices = [{
            "id": row["id"],
            "status": row["openid"] != "0",
            "openid": row["openid"]
        } for row in rows]

        cursor.close()
        connection.close()

        return jsonify(devices)

    except Exception as e:
        print(f"获取设备列表出错: {e}")
        return jsonify({"error": "服务器内部错误"}), 500

# 8. 获取设备总数
@app.route('/getdevicecount', methods=['GET'])
def get_device_count():
    try:
        connection = get_db_connection()
        if not connection:
            return jsonify({"error": "无法连接到数据库"}), 500

        cursor = connection.cursor()
        cursor.execute("SELECT COUNT(*) FROM machine_id")
        count = cursor.fetchone()[0]  # 获取总记录数

        cursor.close()
        connection.close()
        print(count)
        return jsonify({"count": count})

    except Exception as e:
        print(f"获取设备总数出错: {e}")
        return jsonify({"error": "服务器内部错误"}), 500

# 9. 添加设备
@app.route('/webadddevice', methods=['POST'])
def web_add_device():
    data = request.get_json()
    machine_id = data.get('machine_id')

    if not machine_id:
        return jsonify({"message": "缺少 machine_id"}), 400

    conn = get_db_connection()
    if not conn:
        return jsonify({"message": "数据库连接失败"}), 500

    cursor = conn.cursor()
    try:
        # 插入数据（time 自动使用当前时间，openid 默认为 '0'）
        insert_query = """
        INSERT INTO machine_id (machine_id, time, openid)
        VALUES (%s, %s, %s)
        """
        values = (machine_id, datetime.now(), '0')  # openid 默认为 '0'
        cursor.execute(insert_query, values)
        conn.commit()
        return jsonify({"message": "设备添加成功"}), 200
    except Error as e:
        conn.rollback()
        print(f"数据库操作失败: {e}")
        return jsonify({"message": "添加失败，请重试"}), 500
    finally:
        cursor.close()
        conn.close()

# 10. 解绑设备
@app.route('/webunbinddevice', methods=['POST'])
def web_unbind_device():
    data = request.get_json()
    device_machine_id = data.get('id')  # 前端传的是 machine_id，比如 "ZJA41565"

    if not device_machine_id:
        return jsonify({"message": "缺少设备ID"}), 400

    conn = None
    cursor = None

    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        # 👇 根据 machine_id 更新 openid
        update_query = """
        UPDATE machine_id SET openid = '0' WHERE machine_id = %s
        """
        cursor.execute(update_query, (device_machine_id,))
        conn.commit()

        if cursor.rowcount == 0:
            return jsonify({"message": "未找到该设备"}), 404

        return jsonify({"message": "设备已成功解绑"}), 200

    except Error as e:
        if conn:
            conn.rollback()
        print(f"数据库操作失败: {e}")
        return jsonify({"message": "解绑失败，请重试"}), 500

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

# 11. 系统监控
def get_server_status():
    cpu_usage = psutil.cpu_percent(interval=1)
    memory_info = psutil.virtual_memory()
    return {
        'cpuUsage': cpu_usage,
        'memoryUsage': memory_info.percent,
        'status': '正常' if cpu_usage < 90 and memory_info.percent < 90 else '警告'
    }
def check_mysql_connection():
    try:
        connection = get_db_connection()
        cursor = connection.cursor()
        cursor.execute("SELECT 1")
        result = cursor.fetchone()
        if result[0] != 1:
            print("MySQL SELECT 1 测试失败")  # 添加调试输出
            return "查询测试失败", False
        return "正常", True
    except Error as e:
        print(f"MySQL 连接异常: {e}")  # 关键调试输出
        return f"数据库连接错误: {str(e)}", False
    finally:
        if 'connection' in locals() and connection.is_connected():
            cursor.close()
            connection.close()
def check_mongodb_connection():
    try:
        mongo_client.admin.command('ismaster')
        return "正常"
    except ConnectionFailure as e:
        print(f"MongoDB 连接异常: {e}")  # 添加调试输出
        return f"连接失败: {str(e)}"

@app.route('/check', methods=['GET'])
def check():
    server_status = get_server_status()
    db_status, is_db_connected = check_mysql_connection()
    mongo_status = check_mongodb_connection()

    # 构建响应数据
    response_data = {
        'serverStatus': server_status['status'],
        'dbStatus': db_status,
        'mongoStatus': mongo_status,
        'apiStatus': '正常',
        'alarmInfo': '无告警'
    }

    # 如果有任何一项异常，则设置告警信息
    if server_status['status'] == '警告' or not is_db_connected or mongo_status != '正常':
        response_data['alarmInfo'] = '存在问题，请检查系统日志'

    # 总是返回 200，避免前端误判为网络错误
    return jsonify(response_data), 200


# 12. 总览
# 平台核心指标接口
@app.route('/overview', methods=['GET'])
def get_overview():
    connection = get_db_connection()
    if not connection:
        return jsonify({"error": "数据库连接失败"}), 500

    try:
        # ----------------------
        # 1. 查询总用户数
        # ----------------------
        user_count_query = "SELECT COUNT(*) AS total_users FROM Wxuser_data;"
        
        # ----------------------
        # 2. 查询活跃设备数（openid != '0' 为活跃）
        # ----------------------
        active_device_query = """
            SELECT COUNT(*) AS total_active_devices 
            FROM machine_id 
            WHERE openid != '0'; -- openid非零即为活跃设备
        """

        with connection.cursor() as cursor:
            # 执行用户数查询
            cursor.execute(user_count_query)
            user_result = cursor.fetchone()
            total_users = user_result[0] if user_result else 0

            # 执行活跃设备数查询
            cursor.execute(active_device_query)
            device_result = cursor.fetchone()
            total_active_devices = device_result[0] if device_result else 0
            print(total_active_devices)  # 调试信息

        return jsonify({
            "totalUsers": total_users,
            "activeDevices": total_active_devices
        }), 200

    except Error as e:
        print(f"查询数据失败: {e}")
        return jsonify({"error": "数据查询失败"}), 500
    finally:
        if connection.is_connected():
            connection.close()

# 13. 设备分布状态
@app.route('/status-distribution', methods=['GET'])
def get_device_status_distribution():
    connection = get_db_connection()
    if not connection:
        return jsonify({"error": "数据库连接失败"}), 500

    try:
        # 仅通过 openid 字段判断设备状态
        # 正常运行：openid != '0'（表示已绑定用户）
        # 空闲中：openid = '0'（表示未绑定用户）
        query = """
            SELECT 
                SUM(CASE WHEN openid != '0' THEN 1 ELSE 0 END) * 100 / COUNT(*) AS normal,
                SUM(CASE WHEN openid = '0' THEN 1 ELSE 0 END) * 100 / COUNT(*) AS idle
            FROM machine_id;
        """

        with connection.cursor() as cursor:
            cursor.execute(query)
            result = cursor.fetchone()

            # 处理空表或查询结果为空的情况
            if not result:
                return jsonify({
                    "normal": 0.0,
                    "idle": 0.0
                }), 200

            # 确保百分比为浮点数，避免整数除法问题
            normal_percent = float(result[0] or 0)
            idle_percent = float(result[1] or 0)

            # 处理浮点精度问题（如避免出现 0.0000001 之类的数值）
            normal_percent = round(normal_percent, 1)
            idle_percent = round(idle_percent, 1)

            return jsonify({
                "normal": normal_percent,
                "idle": idle_percent
            }), 200

    except Error as e:
        print(f"设备状态查询失败: {e}")
        return jsonify({"error": "数据查询失败"}), 500
    finally:
        if connection.is_connected():
            connection.close()

# 14. 用户年龄分布
@app.route('/age-distribution', methods=['GET'])
def get_age_distribution():
    connection = get_db_connection()
    if not connection:
        return jsonify({"error": "数据库连接失败"}), 500

    try:
        query = """
            SELECT 
                CASE 
                    WHEN age < 18 THEN '未成年（<18岁）'
                    WHEN age BETWEEN 18 AND 30 THEN '青年（18-30岁）'
                    WHEN age BETWEEN 31 AND 50 THEN '中年（31-50岁）'
                    ELSE '老年（>50岁）'
                END AS age_group,
                COUNT(*) AS count
            FROM Wxuser_data
            WHERE age IS NOT NULL
            GROUP BY age_group
            ORDER BY FIELD(age_group, '未成年（<18岁）', '青年（18-30岁）', '中年（31-50岁）', '老年（>50岁）');
        """

        with connection.cursor() as cursor:
            cursor.execute(query)
            results = cursor.fetchall()

            result_dict = {row[0]: row[1] for row in results}
            return jsonify(result_dict), 200

    except Error as e:
        print(f"年龄分布查询失败: {e}")
        return jsonify({"error": "数据查询失败"}), 500
    finally:
        if connection.is_connected():
            connection.close()

# 15. 用户性别分布
@app.route('/gender-distribution', methods=['GET'])
def get_gender_distribution():
    connection = get_db_connection()
    if not connection:
        return jsonify({"error": "数据库连接失败"}), 500

    try:
        query = """
            SELECT 
                CASE gender
                    WHEN 1 THEN '男'
                    WHEN 2 THEN '女'
                    ELSE '未知'
                END AS gender_label,
                COUNT(*) AS count
            FROM Wxuser_data
            GROUP BY gender_label
            ORDER BY gender_label;
        """

        with connection.cursor() as cursor:
            cursor.execute(query)
            results = cursor.fetchall()

            total = sum(row[1] for row in results)
            result_list = [{"gender": g, "count": c, "percentage": round(c / total * 100, 2)} for g, c in results]
            return jsonify(result_list), 200

    except Error as e:
        print(f"性别分布查询失败: {e}")
        return jsonify({"error": "数据查询失败"}), 500
    finally:
        if connection.is_connected():
            connection.close()

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000, debug=True)